/**
 * @author Fang Lu, fanglupku@gmail.com
 * 2012-2-8
 */
package com.autotag.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.autotag.entity.AbstractProvider;

import fanglu.util.StringDealer;

public class AbstractProviderDao {
	private static boolean checkExist(Connection conn,
			AbstractProvider abstractProviderEntity) {
		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm
					.executeQuery("select id from provider where name = '"
							+ abstractProviderEntity.getName() + "'");
			if (rs.next()) {
				return true;// the user already exists
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return true;
		}
	}

	public static boolean insertProvider(Connection conn,
			AbstractProvider abstractProviderEntity) {
		if (abstractProviderEntity.getName() == null
				|| abstractProviderEntity.getRef() == null
				|| abstractProviderEntity.getServiceNum() < 0) {
			return false;
		}
		if (checkExist(conn, abstractProviderEntity)) {
			System.err.println("Provider:" + abstractProviderEntity.getName()
					+ " already exists in database");
			return false;// the user already exists
		}
		try {
			if (abstractProviderEntity.getId() < 0) {
				PreparedStatement pst = conn
						.prepareStatement("insert into provider (name, ref, service_num, status) values (?,?,?,?)");
				pst.setString(1, StringDealer.getStringForMaxLength(AbstractProvider.MAX_LENGTH_NAME,
						abstractProviderEntity.getName()));
				pst.setString(2, StringDealer.getStringForMaxLength(AbstractProvider.MAX_LENGTH_REF,
						abstractProviderEntity.getRef()));
				pst.setInt(3, abstractProviderEntity.getServiceNum());
				pst.setInt(4, abstractProviderEntity.getStatus());
				pst.executeUpdate();
				pst.close();
			} else {
				PreparedStatement pst = conn
						.prepareStatement("insert into provider (id, name, ref, service_num, status) values (?,?,?,?,?)");
				pst.setInt(1, abstractProviderEntity.getId());
				pst.setString(2, StringDealer.getStringForMaxLength(AbstractProvider.MAX_LENGTH_NAME,
						abstractProviderEntity.getName()));
				pst.setString(3, StringDealer.getStringForMaxLength(AbstractProvider.MAX_LENGTH_REF,
						abstractProviderEntity.getRef()));
				pst.setInt(4, abstractProviderEntity.getServiceNum());
				pst.setInt(5, abstractProviderEntity.getStatus());
				pst.executeUpdate();
				pst.close();
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public static boolean updateProvider(Connection conn,
			AbstractProvider abstractProviderEntity) {
		if (abstractProviderEntity.getName() == null
				&& abstractProviderEntity.getId() <= 0) {
			return false;
		}
		try {
			String sqlString = "update provider ";
			if (abstractProviderEntity.getId() > 0) {
				sqlString += "set id=?";
			} else {
				sqlString += "set name=?";
			}
			if (abstractProviderEntity.getRef() != null) {
				sqlString += ",ref=?";
			}
			if (abstractProviderEntity.getServiceNum() > 0) {
				sqlString += ",service_num=?";
			}
			if (abstractProviderEntity.getStatus() > 0) {
				sqlString += ",status=?";
			}
			if (abstractProviderEntity.getName() != null) {
				sqlString += ",name=?";
			}
			if (abstractProviderEntity.getId() > 0) {
				sqlString += " where id=" + abstractProviderEntity.getId();
			} else {
				sqlString += " where name='" + abstractProviderEntity.getName()
						+ "'";
			}
			PreparedStatement pst = conn.prepareStatement(sqlString);
			int index_para = 1;
			if (abstractProviderEntity.getId() > 0) {
				pst.setInt(index_para, abstractProviderEntity.getId());
			} else {
				pst.setString(index_para, StringDealer.getStringForMaxLength(
						AbstractProvider.MAX_LENGTH_NAME, abstractProviderEntity.getName()));
			}
			++index_para;
			if (abstractProviderEntity.getRef() != null) {
				pst.setString(index_para, StringDealer.getStringForMaxLength(
						AbstractProvider.MAX_LENGTH_REF, abstractProviderEntity.getRef()));
				++index_para;
			}
			if (abstractProviderEntity.getServiceNum() > 0) {
				pst.setInt(index_para, abstractProviderEntity.getServiceNum());
				++index_para;
			}
			if (abstractProviderEntity.getStatus() > 0) {
				pst.setInt(index_para, abstractProviderEntity.getStatus());
				++index_para;
			}
			if (abstractProviderEntity.getName() != null) {
				pst.setString(index_para, StringDealer.getStringForMaxLength(
						AbstractProvider.MAX_LENGTH_NAME, abstractProviderEntity.getName()));
				++index_para;
			}

			pst.executeUpdate();
			pst.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public static List<AbstractProvider> getAllAbstractProvider(Connection conn){
		List<AbstractProvider> resultList = new ArrayList<AbstractProvider>();
		String sqlString = "select id,name,ref,service_num,status from provider";
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet resultSet = null;
			resultSet = stmt.executeQuery(sqlString);
			while (resultSet.next()) {
				int thisId = resultSet.getInt(1);
				String thisName = resultSet.getString(2);
				String thisRef = resultSet.getString(3);
				int thisServiceNum = resultSet.getInt(4);
				int thisStatus = resultSet.getInt(5);
				AbstractProvider thisEntity = new AbstractProvider(thisId, thisName, thisRef, thisServiceNum, thisStatus);
				resultList.add(thisEntity);
			}
			resultSet.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultList;
	}
}
